package cn.jdy.plugins.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Service;

import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.exception.BaseException;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.platform.entity.News;

@Service
public class ColumnchartService extends BaseDao<News> {
	
	/***
	 * @author lph
	 * @desc 局直用稿查询,统计各局的投稿数量。
	 * @return
	 */
	public List<Map<String, String>> getCounts(String time) {
		Map<String, Object> map = new HashMap<String, Object>();
		String sql="";
		if(time.length()==4){
			sql = "select d.realname realname,if(isnull(c.c1),0,c.c1) c1 from (select b.realname,count(a.username)  c1 from news a "
		    		+ "RIGHT  JOIN  user b on a.username=b.username where a.isarticle ='1' "
		    		+ "and a.status ='1' and SUBSTR(a.falseCreateTime,1,4)=:time "
		    		+ "group by b.realname  ) c RIGHT JOIN user d on c.realname=d.realname where "
		    		+ "d.username not in ('admin' ,'superadmin','zd1','zd2','zd3','zd4','zd5','gh','bafwzgs','fwh','zhzx','市局','xcfj','fcfj','gxfj','xdfj','djxqfj','ylzfj','xzgafj','zysgaj','ycsgaj','lhksgaj','nzxgaj','bkxgaj','gcxgaj')and d.realname not in ('需要新签收的模块') ORDER BY c1 DESC";
		}
		else{
			sql = "select d.realname realname,if(isnull(c.c1),0,c.c1) c1 from (select b.realname,count(a.username)  c1 from news a "
		    		+ "RIGHT  JOIN  user b on a.username=b.username where a.isarticle ='1' "
		    		+ "and a.status ='1' and SUBSTR(a.falseCreateTime,1,7)=:time "
		    		+ "group by b.realname  ) c RIGHT JOIN user d on c.realname=d.realname where "
		    		+ "d.username not in ('admin' ,'superadmin','zd1','zd2','zd3','zd4','zd5','gh','bafwzgs','fwh','zhzx','市局','xcfj','fcfj','gxfj','xdfj','djxqfj','ylzfj','xzgafj','zysgaj','ycsgaj','lhksgaj','nzxgaj','bkxgaj','gcxgaj')and d.realname not in ('需要新签收的模块') ORDER BY c1 DESC";
		}
	     
	    SQLQuery query = super.em().createSQLQuery(sql.toString());
	    try {
	    	// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
	    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	    	query.setParameter("time", time);
		 
	    } catch(Exception e) {
			BaseLog.e(this.getClass(), "find局直用稿异常!", e);
			throw new BaseException("获得局直用稿异常!", e);
		}

	    return query.list();
	  }
	
	/***
	 * @author lph
	 * @desc 各地用稿查询，统计各科各部门的投稿数量。
	 * @return
	 */
	public List<Map<String, String>> getCount(String time) {
		Map<String, Object> map = new HashMap<String, Object>();
		String sql="";
		if(time.length()==4){
			 sql ="select d.realname realname,if(isnull(c.c1),0,c.c1) c1 from (select b.realname,count(a.username)  c1 from news a "
		    		+ "RIGHT  JOIN  user b on a.username=b.username where a.isarticle ='1' "
		    		+ "and a.status ='1' and SUBSTR(a.falseCreateTime,1,4)=:time "
		    		+ "group by b.realname ) c RIGHT JOIN user d on c.realname=d.realname where d.realname  like '%局%' and d.username not in ('市局' ,'jcfj','cggjfj')"
		    		+ " ORDER BY c1 DESC ";
		}else
		{
			 sql ="select d.realname realname,if(isnull(c.c1),0,c.c1) c1 from (select b.realname,count(a.username)  c1 from news a "
		    		+ "RIGHT  JOIN  user b on a.username=b.username where a.isarticle ='1' "
		    		+ "and a.status ='1' and SUBSTR(a.falseCreateTime,1,7)=:time "
		    		+ "group by b.realname ) c RIGHT JOIN user d on c.realname=d.realname where d.realname  like '%局%' and d.username not in ('市局' ,'jcfj','cggjfj')"
		    		+ " ORDER BY c1 DESC ";
		}
	    
	    SQLQuery query = super.em().createSQLQuery(sql.toString());
	    try {
	    	// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
	    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
	    	query.setParameter("time", time);
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "find各地用稿异常!", e);
			throw new BaseException("获得各地用稿异常!", e);
		}
	   
	    return query.list();
	  }
	
	/***
	 * @author lph
	 * @desc 取年份的数据。
	 * @return
	 */
	public List<Map<String, String>> getyear() {
		Map<String, Object> map = new HashMap<String, Object>();
		//List<String> list;
		String sql ="select DISTINCT(SUBSTR(falseCreateTime,1,4)) as newtime from news "
	    		+ "where isarticle !='' and status ='1'  order by newtime desc";
	    SQLQuery query = super.em().createSQLQuery(sql.toString());
		 try {
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		    	//query.setParameter("time", time);
		} catch (Exception e) {
			// TODO: handle exception
			BaseLog.e(this.getClass(), "查询异常!", e);
		}
	    
	    //list= query.list();
	    return query.list();
	  } 
	
//	public List<Map<String, String>> getMonth(String year) {
//		Map<String, Object> map = new HashMap<String, Object>();
//		//List<String> list;
//		String sql ="select DISTINCT(SUBSTR(falseCreateTime,1,7)) as newtime from news "
//	    		+ "where isarticle !='' and status ='1' and SUBSTR(falseCreateTime,1,4)=:year order by newtime desc";
//	    SQLQuery query = super.em().createSQLQuery(sql.toString());
//		 try {
//			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
//		    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//		    	query.setParameter("year", year);
//		} catch (Exception e) {
//			// TODO: handle exception
//			BaseLog.e(this.getClass(), "查询异常!", e);
//		}
//	    
//	    //list= query.list();
//	    return query.list();
//	  } 
	  
	  /***
		 * @author lph
		 * @desc 查询最大的月份。
		 * @return
		 */
		public List<Map<String, String>> getMonths() {
			Map<String, Object> map = new HashMap<String, Object>();
			//List<String> list;
			String sql ="select DISTINCT(SUBSTR(falseCreateTime,1,7)) as newtime from news "
		    		+ "where isarticle !='' and status ='1' and SUBSTR(falseCreateTime,1,7) order by newtime desc limit 1";
		    SQLQuery query = super.em().createSQLQuery(sql.toString());
			 try {
				// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			    	//query.setParameter("time", time);
			} catch (Exception e) {
				// TODO: handle exception
				BaseLog.e(this.getClass(), "查询异常!", e);
			}
		    
		    //list= query.list();
		    return query.list();
		  } 
		
		/***
		 * @author lph
		 * @desc 浏览排名。
		 * @return
		 */
		public List<Map<String, String>> getLiulan() {
			Map<String, Object> map = new HashMap<String, Object>();
		    String sql ="select * from (SELECT (select realname from user where username='xcfj') aa, xcfj bb from cities "
		    		+ "UNION select (select realname from user where username='fcfj') aa, fcfj bb from cities "
		    		+ "UNION select (select realname from user where username='gxfj') aa, gxfj bb from cities "
		    		+ "UNION select (select realname from user where username='xdfj') aa, xdfj bb from cities "
		    		+ "UNION select (select realname from user where username='djxqfj') aa, djxqfj bb from cities "
		    		+ "UNION select (select realname from user where username='ylzfj') aa, ylzfj bb from cities "
		    		+ "UNION select (select realname from user where username='xzgafj') aa, xzgafj bb from cities "
		    		+ "UNION select (select realname from user where username='zysgaj') aa, zysgaj bb from cities "
		    		+ "UNION select (select realname from user where username='ycsgaj') aa, ycsgaj bb from cities "
		    		+ "UNION select (select realname from user where username='lhksgaj') aa, lhksgaj bb from cities "
		    		+ "UNION select (select realname from user where username='nzxgaj') aa, nzxgaj bb from cities "
		    		+ "UNION select (select realname from user where username='bkxgaj') aa, bkxgaj bb from cities "
		    		+ "UNION select (select realname from user where username='gcxgaj') aa, gcxgaj bb from cities ) a ORDER BY bb desc";
		    SQLQuery query = super.em().createSQLQuery(sql.toString());
		    try {
		    	// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		    	query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "find浏览排名异常!", e);
				throw new BaseException("获得浏览排名异常!", e);
			}
		   
		    return query.list();
		  }
}
